package com.bj58.ecdata.mailreport.templateParse;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;

import com.bj58.ecdata.mailreport.db.CommonDao;
import com.bj58.ecdata.mailreport.model.IESend;
import com.bj58.ecdata.mailreport.model.IETable;
import com.bj58.ecdata.mailreport.model.IETableSearch;
import com.bj58.ecdata.mailreport.threads.IESendTimerTask;
import com.bj58.ecdata.mailreport.util.DateUtil;
import com.bj58.ecdata.mailreport.util.EmailReportUtil;
import com.bj58.ecdata.mailreport.util.MathEval;
import com.bj58.ecdata.mailreport.util.MyPathUtil;
import com.bj58.ecdata.mailreport.util.SortUtil;
import com.bj58.ecdata.mailreport.util.VelocityTools;

public class IETemplateParse {
	
//	private IETableService IETemplateContentService = ServiceLocator.get(IETableService.class);
	private CommonDao comDao = new CommonDao();
	private EmailReportUtil eru = new EmailReportUtil();
	private VelocityTools vt = new VelocityTools();
	
	/**模板路径*/
	public static String templateMainPath = MyPathUtil.getIEPath()+"emailReportMain.html"; //模板主体
	static String sendTemplateMain1 = MyPathUtil.getIEPath()+"sendTemplate1.html"; //发送模板1
	static String sendTemplateMain2 = MyPathUtil.getIEPath()+"sendTemplate2.html"; //发送模板2
	static String sendTemplateMain3 = MyPathUtil.getIEPath()+"sendTemplate3.html"; //发送模板3
	static String sendTemplateMain4 = MyPathUtil.getIEPath()+"sendTemplate4.html"; //发送模板4
	static String sendTemplateMain5 = MyPathUtil.getIEPath()+"sendTemplate5.html"; //发送模板4
	static String sendTemplateMain6 = MyPathUtil.getIEPath()+"sendTemplate6.html"; //发送模板4
	
	/**邮件发送  表格内容渲染 - 模板1 ：汇总模板
	 */
	public String getTemplateContentOne(IESend ieSend,IETable ieTable,List<IETableSearch> ieTablerSearchList,String searchDate)throws Exception{
		Map<String,Object> conMap = new HashMap<String,Object>();
		String content = MyPathUtil.getTemByPath(sendTemplateMain1);
		
		//查询日期集
		Date  sDate = DateUtil.strToDate(searchDate, "yyyy-MM-dd");
		String today = DateUtil.getStringDate(sDate,"yyyy-MM-dd");//查询日期（今）
		conMap.put("date", today);
		String yesterday = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 1),"yyyy-MM-dd");//查询日期（昨）
		String lastWeek = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 7),"yyyy-MM-dd");//查询日期（上周）
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(yesterday);dateList.add(lastWeek);		
		
		/**表名*/
		conMap.put("tableName",replaceDate(ieTable.getTableName(), today, yesterday, ""));
		conMap.put("tableComment", ieTable.getTableComment());
		/**TH*/
		String[] thTemps = ieTable.getTemplateTh().split(",");
		List<String> thList = new ArrayList<String>(Arrays.asList(thTemps));
		conMap.put("thList",thList);
		
		
		/**tds*/
		String[] tdTemps = ieTable.getTemplateTd().split(";");
		
		List<String> rowHeadList=new ArrayList<String>();	//自己定义的行头，如'总体'。报表是按行头排序还是按第一列数据排序？？？有待考虑
		List<String> rowHeadListGB=new ArrayList<String>();	//group by出来的行头,如city.
		
		Map<String,String>  tdMap = new HashMap<String, String>(); //指标：行键，如A1：精准
		Map<String,String>  rowTdMap = new HashMap<String, String>(); //指标：行索引 如A1：A1,null,B1,null,null 
		
		for(int j=0;j<tdTemps.length;j++){
			String tds = tdTemps[j];
			int index=tds.indexOf(",");
			String key= tds.substring(index+1);
			String value = tds.substring(0,index);
			if(!value.contains("groupBy"))
				rowHeadList.add(value);
			for(String s: key.split(",")){
				tdMap.put(s, value);
				rowTdMap.put(s,key);
			}
		}

		
		/**valuesList*/
		
		List<IETableSearch> speSearchList = new ArrayList<IETableSearch>();//特殊计算，跨行
		List<IETableSearch> innerSpeList = new ArrayList<IETableSearch>();//特殊计算,行内
		List<IETableSearch> normalSearchList = new ArrayList<IETableSearch>();//正常计算
		HashMap<String,String> indexTypeMap=new HashMap<String,String>(); //特殊计算指标index:type  type:1百分比,0非百分比
		for(IETableSearch vo : ieTablerSearchList){
			int isSpe = vo.getIsSpe();
			if(isSpe == 1){
				String indexs[] = vo.getIndexs().split("/");
				String evals[] = vo.getSearchSql().split(";");
				for(int i=0; i < indexs.length; i++){
					String index=indexs[i].trim();
					String evalSQl = evals[i];
					indexTypeMap.put(index.split(":")[0], index.split(":")[1]);
					String rowIndexs=rowTdMap.get(index.split(":")[0]);
					Set<String> rowTdSet = new HashSet<String>();
					rowTdSet.addAll(Arrays.asList(rowIndexs.replace("null", "").split(",")));
					IETableSearch splitedVo = vo.clone();
					splitedVo.setIndexs(index);
					splitedVo.setSearchSql(evalSQl);
					if(MathEval.checkIndexBySelfRow(evalSQl, rowTdSet)){
						innerSpeList.add(splitedVo);
					}else{
						speSearchList.add(splitedVo);
					}
				}
			}else{
				normalSearchList.add(vo);
			}
		}
		
		/**普通数据查询*/
		//key = 行表头;日期  ； value = 指标：值
		Map<String,Map<String,Object>> tempDataMap = new HashMap<String, Map<String,Object>>();
		boolean hasGroup=false;
		boolean hasBeginDate=false;
		for(String date : dateList){
			String day = date.replace("-", ""); //默认天
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", ""); //默认月份
			String year =  date.substring(0,date.indexOf("-")); //默认年份
			String lastMonth = DateUtil.getMonthBefore(date, "yyyy-MM-dd", 1); //上月月份
			String lastDay=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 1); //前一天
			String tomorrow=DateUtil.strToStrAfter(date, "yyyy-MM-dd", 1); //后一天
			for(IETableSearch vo : normalSearchList){
				//sql deal
				String sql = vo.getSearchSql();
				try{
				sql = sql.replace("@", "'");
				sql = sql.replace("$", "\"");
				String realDay = day, realYear=year, realMonth=month;//真实查询的年，月
				if(sql.contains("#date0#")){
					realDay = lastDay.replace("-", "");
					realMonth = lastDay.substring(0,lastDay.lastIndexOf("-")).replace("-", "");
					realYear = lastDay.substring(0,lastDay.indexOf("-"));
				}else if(sql.contains("#date2#")){
					realDay = tomorrow.replace("-", "");
					realMonth = tomorrow.substring(0,tomorrow.lastIndexOf("-")).replace("-", "");
					realYear = tomorrow.substring(0,tomorrow.indexOf("-"));
				}

				sql = replaceDate(sql, date, lastDay, tomorrow);
				//查询指标
				String indexs = vo.getIndexs();
				List<String> nameList = new ArrayList<String>(Arrays.asList(indexs.split("/")));
				//如果SQL是group by类型  则要在nameList第一位加入groupBy 进行占位
				boolean isGroup=false;
				if(StringUtils.containsIgnoreCase(tdMap.get(nameList.get(0).split(":")[0]),"group")){
					nameList.add(0, "groupBy");
					isGroup=true;
				}
				
				List<Map<String,Object>>tempList = new ArrayList<Map<String,Object>>();
				if(sql.contains("#beginDate#")){
					if(sql.contains("#endDate#")&&isGroup)
						hasBeginDate=true;
					String beginDate=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 7);
					String endDate=date;
					if(sql.contains("yyyyMMdd")){	//天表
						List<String> daySearchList = DateUtil.getDayList(beginDate, endDate);
						endDate = DateUtil.strToStrAfter(endDate, "yyyy-MM-dd", 1);	//SQL中配置的是stat_date<endDate, 所以向后推一天
						for(String curday : daySearchList){
							String newsql = sql.replace("yyyyMMdd", curday);
							newsql=newsql.replace("#beginDate#", beginDate).replace("#endDate#", endDate);
							tempList.addAll(comDao.searchDataForIE(newsql, nameList, vo.getDbId()));
						}
						
					}
					else{		//月表
						List<String> monthSearchList = DateUtil.subSearchDate(beginDate, endDate);
						for(String con : monthSearchList){
							String[] conArrays = con.split(";");
							String curMonth = conArrays[0];
							String start = conArrays[1];
							String end = conArrays[2];
							String newsql = sql.replace("yyyyMM0", lastMonth);
							newsql = newsql.replace("yyyyMM", curMonth);
							newsql = newsql.replace("yyyy", curMonth.substring(0,4));
							newsql=newsql.replace("#beginDate#", start).replace("#endDate#", end);
							tempList.addAll(comDao.searchDataForIE(newsql, nameList, vo.getDbId()));
						}
					}
					
				}else{
					
					sql = sql.replace("yyyyMM0", lastMonth);
					sql = sql.replace("yyyyMMdd", realDay);
					sql = sql.replace("yyyyMM", realMonth);
					sql = sql.replace("yyyy",realYear); 
					tempList = comDao.searchDataForIE(sql, nameList, vo.getDbId());
//					System.out.println(sql);
				}
				if(tempList.isEmpty() && date.equals(today) && tempDataMap.isEmpty())
					throw new NullPointerException();
				if(!isGroup && (!tempList.isEmpty() || date.equals(today))){ //正常,查询日期为今日，或者不是今日但有数据
					Map<String,Object> valueMap = tempList.get(0);
					for(String  colName : nameList){
						if(colName.split(":").length > 1)
							indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
						colName=colName.split(":")[0];
						if("groupBy".equals(colName)){
							continue;
						}
						String rowKey = "";
						//确认该指标归属哪一行
						if(tdMap.containsKey(colName)){
							rowKey = tdMap.get(colName);
						}
						rowKey = rowKey + ";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						temp.put(colName, valueMap.get(colName));
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}else{ //group by sql deal
					hasGroup=true;
					for (Map<String, Object> valueMap : tempList) {
						if(StringUtils.equals("--", String.valueOf(valueMap.get("groupBy"))))
							continue;
						if(!rowHeadListGB.contains(valueMap.get("groupBy").toString())){	//行头未出现过
							rowHeadListGB.add(valueMap.get("groupBy").toString());
						}
						String rowKey = valueMap.get("groupBy") + ";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						
						for(String colName : nameList){
							if(colName.split(":").length > 1)
								indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
							colName=colName.split(":")[0];
							if("groupBy".equals(colName)){
								continue;
							}
							Object colValue = valueMap.get(colName);
							temp.put(colName, colValue);
						}
//						System.out.println(rowKey);
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}
				}catch(Exception e){
					throw new Exception(sql,e);
				}
			}
			if(hasBeginDate)
				break;
		}
		
		
		/**特殊计算公式指标处理 */
		//tempDataMap（已计算的普通数据）结构 示例
		//二手市场;2014-05-01={D2=5853, D3=32.74598802753956, D1=191662.26792518905}, 
		//二手车;2014-04-25={B3=65.0348026631048, B2=1706, B1=110949.3733432568}, 
		//黄页;2014-04-25={C1=729912.8916443274, C2=23508}, 
		//招聘;2014-04-25={C1=429912.49887443, C2=13208}, 
		//二手车;2014-05-02={B3=65.80859929546526, B2=1418, B1=93316.59380096974}, 
		
		//日期：指标：值 ，如'2014-07-31':{A1:5400, B2:23.5}
		Map<String,Map<String,Object>> indexDataMap =  new HashMap<String, Map<String,Object>>(); 
		for(Iterator<String> it = tempDataMap.keySet().iterator(); it.hasNext();){
			String key = it.next();
			Map<String,Object> valueMap = tempDataMap.get(key);
			String dateKey = key.split(";")[1]; 
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(dateKey)){
				sumValueMap = indexDataMap.get(dateKey);
			}
			
			for(Iterator<String> it3 = valueMap.keySet().iterator(); it3.hasNext();){
				String index = it3.next();
				double value = 0;
				String strVal=valueMap.get(index).toString();
				try{
					
					if(strVal.equals("--"))
						value=0;
					else value = Double.valueOf(strVal);
				}catch(Exception e){
				}
				if(sumValueMap.containsKey(index)){
					value = value + Double.valueOf(sumValueMap.get(index).toString());
				}
				sumValueMap.put(index, value);
			}
			indexDataMap.put(dateKey, sumValueMap);
		}
			
		/**特殊计算公式指标处理 */
		for(String date:dateList){
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(date)){
				sumValueMap = indexDataMap.get(date);
			}

			List<IETableSearch> speList=new ArrayList<IETableSearch>();
			for(IETableSearch vo : speSearchList){
				String index = vo.getIndexs().trim().split(":")[0];
				String formula = vo.getSearchSql();
				if(MathEval.checkIndexBySelfRow(formula, sumValueMap.keySet())){
					//检查该指标的计算公式是否仅依赖已有指标
					String countFormula = formula;
					//替换值  放入计算公式
					for(Iterator<String> it2 = sumValueMap.keySet().iterator() ; it2.hasNext();){
						String key2 = it2.next();
						Object value = sumValueMap.get(key2);
						countFormula =replaceKey(countFormula,key2,String.valueOf(value));
					}
					String famount = "--";
					if(!StringUtils.contains(countFormula, "--"))
						famount = String.valueOf(MathEval.eval(countFormula));
					String rowKey=tdMap.get(index)+";"+date;
					Map<String,Object> valueMap = tempDataMap.get(rowKey);
					if(valueMap==null)
						valueMap = new HashMap<String, Object>();
					valueMap.put(index, famount);
					tempDataMap.put(rowKey, valueMap);
				}else{
					speList.add(vo);
				}
				if(speList.size()>0){
					for(IETableSearch v:speList)
						IESendTimerTask.getLogger().error(v.getIndexs()+": "+v.getSearchSql());
				}
		  }
		}
		
//		//汇总型特殊计算   
//		if(summnyList.size() > 0 && !summnyList.isEmpty()){
//			//先将tempDataMap数据 以日期为维度，将各指标进行SUM
//			Map<String,Map<String,Object>> sumTempDataMap = new HashMap<String, Map<String,Object>>();
//			
//			for(Iterator<String> it = tempDataMap.keySet().iterator(); it.hasNext();){
//				String key = it.next();
//				Map<String,Object> valueMap = tempDataMap.get(key);
//				String dateKey = key.split(";")[1];
//				
//				Map<String,Object> sumValueMap = new HashMap<String, Object>();
//				if(sumTempDataMap.containsKey(dateKey)){
//					sumValueMap = sumTempDataMap.get(dateKey);
//				}
//				
//				for(Iterator<String> it3 = valueMap.keySet().iterator(); it3.hasNext();){
//					String index = it3.next();
//					double value = Double.valueOf(valueMap.get(index).toString());
//					if(sumValueMap.containsKey(index)){
//						value = value + Double.valueOf(sumValueMap.get(index).toString());
//					}
//					sumValueMap.put(index, value);
//				}
//				sumTempDataMap.put(dateKey, sumValueMap);
//			}
//		}
		/**计算所有指标的同环比*/
		Map<String,Map<String,String>> hasCompMap = eru.conformDataForIETypeOne(ieSend,tempDataMap,indexTypeMap, dateList);
		
		/**根据TDS来确定指标顺序及同环比是否展示*/
		List<String> dataGb = new ArrayList<String>(); //group by 的数据
		List<String> dataNoneGb = new ArrayList<String>(); //非group by 的数据
		for(String rowKey:rowHeadList){
			String result = getResultStr(rowKey, hasCompMap, rowTdMap, indexTypeMap,thTemps, hasGroup, 1);
			dataNoneGb.add(result);
		}
		for(String rowKey:rowHeadListGB){
			if(!hasCompMap.containsKey(rowKey))
				continue;
			String result = getResultStr(rowKey, hasCompMap, rowTdMap, indexTypeMap,thTemps, hasGroup, 1);
			dataGb.add(result);
		}
		
		
		List<String> finalData = new ArrayList<String>(); //最终的数据
		if(ieTable.getDataOrder() > 0){ //统一按第n列排序
			dataNoneGb.addAll(dataGb);
			finalData = SortUtil.sortIEList(dataNoneGb, ieTable.getDataOrder(), 2);
		}else if(ieTable.getDataOrder() == -1){ //排序group by的行，非group by的放在最上方
			dataNoneGb.addAll(SortUtil.sortIEList(dataGb, 1, 2));
			finalData=dataNoneGb;
		}else if(ieTable.getDataOrder() == -2){ //排序group by的行，非group by的放在最下方
			dataGb=SortUtil.sortIEList(dataGb, 1, 2);
			dataGb.addAll(dataNoneGb);
			finalData=dataGb;
		}else if(ieTable.getDataOrder() == -3){ //不排序group by的行，非group by的放在最上方
			dataNoneGb.addAll(dataGb);
			finalData=dataNoneGb;
		}else if(ieTable.getDataOrder() == -4){ //不排序group by的行，非group by的放在最下方
			dataGb.addAll(dataNoneGb);
			finalData=dataGb;
		}else if(ieTable.getDataOrder() == -5){//按行头升序
			dataNoneGb.addAll(dataGb);
			finalData = SortUtil.sortIEListForStr(dataNoneGb, 0, 1);
		}else if(ieTable.getDataOrder() == -6){//按行头降序
			dataNoneGb.addAll(dataGb);
			finalData = SortUtil.sortIEListForStr(dataNoneGb, 0, 2);
		}
		
//		/**默认按照第一列指标值降序排序*/
//		finalData = SortUtil.sortIEList(finalData, 1, 2);
		conMap.put("valuesList",finalData);
		
		content = VelocityTools.render(content, conMap);
		return content;
	}
	
	/**邮件发送  表格内容渲染 - 模板4 ：分类展现模板
	 */
	public String getTemplateContentFour(IESend ieSend,IETable ieTable,List<IETableSearch> ieTablerSearchList,String searchDate)throws Exception{
		Map<String,Object> conMap = new HashMap<String,Object>();
		String content = MyPathUtil.getTemByPath(sendTemplateMain4);

		/**TH*/
		String[] thTemps = ieTable.getTemplateTh().split(",");
		String[] thTwoTemps = ieTable.getTemplateThTwo().replace(";", ",").split(",");
		List<String> thList = new ArrayList<String>(Arrays.asList(thTemps));
		List<String> thTwoList = new ArrayList<String>(Arrays.asList(thTwoTemps));
		conMap.put("thList",thList);
		conMap.put("thTwoList",thTwoList);
		ArrayList<Integer> colsList=new ArrayList<Integer>();
		String[] colGroup=ieTable.getTemplateThTwo().split(";");
		for(String s:colGroup){
			colsList.add(s.split(",").length);
		}
		conMap.put("colspan",colsList);
		
		/**tds*/
		String[] tdTemps = ieTable.getTemplateTd().split(";");
		
		List<String> rowHeadList=new ArrayList<String>();	//自己定义的行头，如'总体'。报表是按行头排序还是按第一列数据排序？？？有待考虑
		List<String> rowHeadListGB=new ArrayList<String>();	//group by出来的行头,如city.
		
		Map<String,String>  tdMap = new HashMap<String, String>(); //指标：行键，如A1：精准
		Map<String,String>  rowTdMap = new HashMap<String, String>(); //指标：行索引 如A1：A1,null,B1,null,null 
		
		for(int j=0;j<tdTemps.length;j++){
			String tds = tdTemps[j];
			int index=tds.indexOf(",");
			String key= tds.substring(index+1);
			String value = tds.substring(0,index);
			if(!value.contains("groupBy"))
				rowHeadList.add(value);
			for(String s: key.split(",")){
				tdMap.put(s, value);
				rowTdMap.put(s,key);
			}
		}

		
		/**valuesList*/
		//查询日期集
		Date  sDate = DateUtil.strToDate(searchDate, "yyyy-MM-dd");
		String today = DateUtil.getStringDate(sDate,"yyyy-MM-dd");//查询日期（今）
		conMap.put("date", today);
		String yesterday = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 1),"yyyy-MM-dd");//查询日期（昨）
		String lastWeek = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 7),"yyyy-MM-dd");//查询日期（上周）
		/**表名*/
		conMap.put("tableName",replaceDate(ieTable.getTableName(), today, yesterday, ""));
		conMap.put("tableComment", ieTable.getTableComment());
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(yesterday);dateList.add(lastWeek);
		
		List<IETableSearch> speSearchList = new ArrayList<IETableSearch>();//特殊计算，跨行
		List<IETableSearch> innerSpeList = new ArrayList<IETableSearch>();//特殊计算,行内
		List<IETableSearch> normalSearchList = new ArrayList<IETableSearch>();//正常计算
		HashMap<String,String> indexTypeMap=new HashMap<String,String>(); //特殊计算指标index:type  type:1百分比,0非百分比
		for(IETableSearch vo : ieTablerSearchList){
			int isSpe = vo.getIsSpe();
			if(isSpe == 1){
					String indexs[] = vo.getIndexs().split("/");
					String evals[] = vo.getSearchSql().split(";");
					for(int i=0; i < indexs.length; i++){
						String index=indexs[i].trim();
						String evalSQl = evals[i];
						indexTypeMap.put(index.split(":")[0], index.split(":")[1]);
						String rowIndexs=rowTdMap.get(index.split(":")[0]);
						Set<String> rowTdSet = new HashSet<String>();
						rowTdSet.addAll(Arrays.asList(rowIndexs.replace("null", "").split(",")));
						IETableSearch splitedVo = vo.clone();
						splitedVo.setIndexs(index);
						splitedVo.setSearchSql(evalSQl);
						if(MathEval.checkIndexBySelfRow(evalSQl, rowTdSet)){
							innerSpeList.add(splitedVo);
						}else{
							speSearchList.add(splitedVo);
						}
					}
			}else{
				normalSearchList.add(vo);
			}
		}
		
		/**普通数据查询*/
		//key = 行表头;日期  ； value = 指标：值
		Map<String,Map<String,Object>> tempDataMap = new HashMap<String, Map<String,Object>>();
		boolean hasGroup=false;
		boolean hasBeginDate=false;
		for(String date : dateList){
			String day = date.replace("-", ""); //默认天
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", ""); //默认月份
			String year =  date.substring(0,date.indexOf("-")); //默认年份
			String lastMonth = DateUtil.getMonthBefore(date, "yyyy-MM-dd", 1); //上月月份
			String lastDay=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 1); //前一天
			String tomorrow=DateUtil.strToStrAfter(date, "yyyy-MM-dd", 1); //后一天
			for(IETableSearch vo : normalSearchList){
				//sql deal
				String sql = vo.getSearchSql();
				try{
				sql = sql.replace("@", "'");
				sql = sql.replace("$", "\"");
				String realDay = day, realYear=year, realMonth=month;//真实查询的年，月
				if(sql.contains("#date0#")){
					realDay = lastDay.replace("-", "");
					realMonth = lastDay.substring(0,lastDay.lastIndexOf("-")).replace("-", "");
					realYear = lastDay.substring(0,lastDay.indexOf("-"));
				}else if(sql.contains("#date2#")){
					realDay = tomorrow.replace("-", "");
					realMonth = tomorrow.substring(0,tomorrow.lastIndexOf("-")).replace("-", "");
					realYear = tomorrow.substring(0,tomorrow.indexOf("-"));
				}

				sql = replaceDate(sql, date, lastDay, tomorrow);
				//查询指标
				String indexs = vo.getIndexs();
				List<String> nameList = new ArrayList<String>(Arrays.asList(indexs.split("/")));
				//如果SQL是group by类型  则要在nameList第一位加入groupBy 进行占位
				boolean isGroup=false;
				if(StringUtils.containsIgnoreCase(tdMap.get(nameList.get(0).split(":")[0]),"group")){
					nameList.add(0, "groupBy");
					isGroup=true;
				}
				
				List<Map<String,Object>>tempList = new ArrayList<Map<String,Object>>();
				if(sql.contains("#beginDate#")){
					if(sql.contains("#endDate#") && isGroup)
						hasBeginDate=true;
					String beginDate=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 7);
					String endDate=date;
					if(sql.contains("yyyyMMdd")){	//天表
						List<String> daySearchList = DateUtil.getDayList(beginDate, endDate);
						endDate = DateUtil.strToStrAfter(endDate, "yyyy-MM-dd", 1);	//SQL中配置的是stat_date<endDate, 所以向后推一天
						for(String curday : daySearchList){
							String newsql = sql.replace("yyyyMMdd", curday);
							newsql=newsql.replace("#beginDate#", beginDate).replace("#endDate#", endDate);
							tempList.addAll(comDao.searchDataForIE(newsql, nameList, vo.getDbId()));
						}
					}
					else{		//月表
						List<String> monthSearchList = DateUtil.subSearchDate(beginDate, endDate);
						for(String con : monthSearchList){
							String[] conArrays = con.split(";");
							String curMonth = conArrays[0];
							String start = conArrays[1];
							String end = conArrays[2];
							String newsql = sql.replace("yyyyMM0", lastMonth);
							newsql = newsql.replace("yyyyMM", curMonth);
							newsql = newsql.replace("yyyy", curMonth.substring(0,4));
							newsql=newsql.replace("#beginDate#", start).replace("#endDate#", end);
							tempList.addAll(comDao.searchDataForIE(newsql, nameList, vo.getDbId()));
						}
					}
				}else{
					sql = sql.replace("yyyyMM0", lastMonth);
					sql = sql.replace("yyyyMMdd", realDay);
					sql = sql.replace("yyyyMM", realMonth);
					sql = sql.replace("yyyy",realYear); 
					tempList = comDao.searchDataForIE(sql, nameList, vo.getDbId());
//					System.out.println(sql);
				}
				if(tempList.isEmpty() && date.equals(today) && tempDataMap.isEmpty())
					throw new NullPointerException();
				if(!isGroup  && (!tempList.isEmpty() || date.equals(today))){ //正常,查询日期为今日，或者不是今日但有数据
					Map<String,Object> valueMap = tempList.get(0);
					for(String  colName : nameList){
						if(colName.split(":").length > 1)
							indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
						colName=colName.split(":")[0];
						if("groupBy".equals(colName)){
							continue;
						}
						String rowKey = "";
						//确认该指标归属哪一行
						if(tdMap.containsKey(colName)){
							rowKey = tdMap.get(colName);
						}
						rowKey = rowKey + ";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						temp.put(colName, valueMap.get(colName));
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}else{ //group by sql deal
					hasGroup=true;
					for (Map<String, Object> valueMap : tempList) {
						if(StringUtils.equals("--", String.valueOf(valueMap.get("groupBy"))))
							continue;
						if(!rowHeadListGB.contains(valueMap.get("groupBy").toString())){	//行头未出现过
							rowHeadListGB.add(valueMap.get("groupBy").toString());
						}
						String rowKey = valueMap.get("groupBy") + ";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						
						for(String colName : nameList){
							if(colName.split(":").length > 1)
								indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
							colName=colName.split(":")[0];
							if("groupBy".equals(colName)){
								continue;
							}
							Object colValue = valueMap.get(colName);
							temp.put(colName, colValue);
						}
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}
				}catch(Exception e){
					throw new Exception(sql,e);
				}
			}
			if(hasBeginDate)
				break;
		}
		
		
		/**特殊计算公式指标处理 */
		//tempDataMap（已计算的普通数据）结构 示例
		//二手市场;2014-05-01={D2=5853, D3=32.74598802753956, D1=191662.26792518905}, 
		//二手车;2014-04-25={B3=65.0348026631048, B2=1706, B1=110949.3733432568}, 
		//黄页;2014-04-25={C1=729912.8916443274, C2=23508}, 
		//招聘;2014-04-25={C1=429912.49887443, C2=13208}, 
		//二手车;2014-05-02={B3=65.80859929546526, B2=1418, B1=93316.59380096974}, 
		
		//日期：指标：值 ，如'2014-07-31':{A1:5400, B2:23.5}
		Map<String,Map<String,Object>> indexDataMap =  new HashMap<String, Map<String,Object>>(); 
		for(Iterator<String> it = tempDataMap.keySet().iterator(); it.hasNext();){
			String key = it.next();
			Map<String,Object> valueMap = tempDataMap.get(key);
			String dateKey = key.split(";")[1]; 
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(dateKey)){
				sumValueMap = indexDataMap.get(dateKey);
			}
			
			for(Iterator<String> it3 = valueMap.keySet().iterator(); it3.hasNext();){
				String index = it3.next();
				double value = 0;
				String strVal=valueMap.get(index).toString();
				try{
					 value = Double.valueOf(strVal);
				}catch(Exception e){
				}
				if(sumValueMap.containsKey(index)){
					value = value + Double.valueOf(sumValueMap.get(index).toString());
				}
				sumValueMap.put(index, value);
			}
			indexDataMap.put(dateKey, sumValueMap);
		}
		
		/**特殊计算公式指标处理 */
		for(String date:dateList){
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(date)){
				sumValueMap = indexDataMap.get(date);
			}

			List<IETableSearch> speList=new ArrayList<IETableSearch>();
			for(IETableSearch vo : speSearchList){
				String index = vo.getIndexs().trim().split(":")[0];
				String formula = vo.getSearchSql();
				if(MathEval.checkIndexBySelfRow(formula, sumValueMap.keySet())){
					//检查该指标的计算公式是否仅依赖已有指标
					String countFormula = formula;
//					System.out.println(countFormula);
					//替换值  放入计算公式
					for(Iterator<String> it2 = sumValueMap.keySet().iterator() ; it2.hasNext();){
						String key2 = it2.next();
						Object value = sumValueMap.get(key2);
						countFormula =replaceKey(countFormula,key2,String.valueOf(value));
					}
					String famount = "--";
					if(!StringUtils.contains(countFormula, "--"))
						famount = String.valueOf(MathEval.eval(countFormula));
					String rowKey=tdMap.get(index)+";"+date;
					Map<String,Object> valueMap = tempDataMap.get(rowKey);
					if(valueMap==null)
						valueMap = new HashMap<String, Object>();
					valueMap.put(index, famount);
					tempDataMap.put(rowKey, valueMap);
				}else{
					speList.add(vo);
				}
				if(speList.size()>0){
					for(IETableSearch v:speList)
						IESendTimerTask.getLogger().error(v.getIndexs()+": "+v.getSearchSql());
				}
		  }
		}
		/**计算所有指标的同环比*/
		Map<String,Map<String,String>> hasCompMap = eru.conformDataForIETypeOne(ieSend,tempDataMap, indexTypeMap,dateList);
		/**根据TDS来确定指标顺序及同环比是否展示*/
		List<String> dataGb = new ArrayList<String>(); //group by 的数据
		List<String> dataNoneGb = new ArrayList<String>(); //非group by 的数据
		for(String rowKey:rowHeadList){
			String result = getResultStr(rowKey, hasCompMap, rowTdMap, indexTypeMap, thTwoTemps,hasGroup, 4);
			dataNoneGb.add(result);
		}
		for(String rowKey:rowHeadListGB){
			if(!hasCompMap.containsKey(rowKey))
				continue;
			String result = getResultStr(rowKey, hasCompMap, rowTdMap, indexTypeMap, thTwoTemps,hasGroup, 4);
			dataGb.add(result);
		}
		
		
		List<String> finalData = new ArrayList<String>(); //最终的数据
		if(ieTable.getDataOrder() > 0){ //统一按第n列排序
			dataNoneGb.addAll(dataGb);
			finalData = SortUtil.sortIEList(dataNoneGb, ieTable.getDataOrder(), 2);
		}else if(ieTable.getDataOrder() == -1){ //排序group by的行，非group by的放在最上方
			dataNoneGb.addAll(SortUtil.sortIEList(dataGb, 1, 2));
			finalData=dataNoneGb;
		}else if(ieTable.getDataOrder() == -2){ //排序group by的行，非group by的放在最下方
			dataGb=SortUtil.sortIEList(dataGb, 1, 2);
			dataGb.addAll(dataNoneGb);
			finalData=dataGb;
		}else if(ieTable.getDataOrder() == -3){ //不排序group by的行，非group by的放在最上方
			dataNoneGb.addAll(dataGb);
			finalData=dataNoneGb;
		}else if(ieTable.getDataOrder() == -4){ //不排序group by的行，非group by的放在最下方
			dataGb.addAll(dataNoneGb);
			finalData=dataGb;
		}else if(ieTable.getDataOrder() == -5){//按行头升序
			dataNoneGb.addAll(dataGb);
			finalData = SortUtil.sortIEListForStr(dataNoneGb, 0, 1);
		}else if(ieTable.getDataOrder() == -6){//按行头降序
			dataNoneGb.addAll(dataGb);
			finalData = SortUtil.sortIEListForStr(dataNoneGb, 0, 2);
		}
		
//		/**默认按照第一列指标值降序排序*/
//		finalData = SortUtil.sortIEList(finalData, 1, 2);
		conMap.put("valuesList",finalData);
//		
//		/**根据TDS来确定指标顺序及同环比是否展示*/
//		for(Iterator<String> it = hasCompMap.keySet().iterator(); it.hasNext();){
//			String rowKey = it.next();
//			
//			Map<String,String> valuesMap = hasCompMap.get(rowKey);
//			String showIndexs = ""; // like B1,null,null,B2,null,null,B3,null
//			
//			//抽取该列第一个指标 判断该指标所属的正常列排序是什么
//			for(Iterator<String> it2 = valuesMap.keySet().iterator(); it2.hasNext();){
//				String index = it2.next();
//				
//				if(rowTdMap.containsKey(index)){
//					showIndexs = rowTdMap.get(index);
//				}
//				if(!"".equals(showIndexs)){
//					break;
//				}
//			}
//			
//			//结合 th和td 取出展示出来的指标及同比环比
//			StringBuffer tempValue = new StringBuffer();
//			tempValue.append(rowKey+";");
//			
//			String[] showIndexTemp = showIndexs.split(",");
//			
//			String lastIndex = "";
//			for (int i = 0 ; i< showIndexTemp.length; i++) {
//				String showIndex = showIndexTemp[i];
//				
//				String appValue =  "";
//				if(!"null".equals(showIndex)){ //正常值
////					System.out.println(rowKey+";"+showIndex);
//					if(!valuesMap.containsKey(showIndex) && hasGroup)
//						appValue = "0";
//					else 
//						appValue = valuesMap.get(showIndex).split(";")[0];
//					String type=indexTypeMap.get(showIndex);
//					if("1".equals(type)){ //如果是百分比类型
//						appValue=vt.comRate(appValue);
//					}
//					lastIndex = showIndex;
//				}else{ //同比、周比
//					String thValue = thTwoTemps[i];
//					if(!valuesMap.containsKey(lastIndex) && hasGroup)
//						appValue = "0%";
//					else if("日比".equals(thValue)){
//						appValue = valuesMap.get(lastIndex).split(";")[1];
//					}else if("周比".equals(thValue)){
//						appValue = valuesMap.get(lastIndex).split(";")[2];
//					}
//					
//				}
//				tempValue.append(appValue+";");
//			}
//		
//			finalData.add(tempValue.toString());
//		}
//		/**默认按照第一列指标值降序排序*/
//		finalData = SortUtil.sortIEList(finalData, 1, 2);
//		conMap.put("valuesList",finalData);
		
		content = VelocityTools.render(content, conMap);
		return content;
	}

	
	/**邮件发送  表格内容渲染 - 模板2 ：详细指标模板
	 */
	public String getTemplateContentTwo(IESend ieSend,IETable ieTable,List<IETableSearch> ieTablerSearchList,String searchDate)throws Exception{
		Map<String,Object> conMap = new HashMap<String,Object>();
		String content = MyPathUtil.getTemByPath(sendTemplateMain2);

		/**TH*/
		String[] thTemps = ieTable.getTemplateTh().split(",");
		List<String> thList = new ArrayList<String>(Arrays.asList(thTemps));
		conMap.put("thList",thList);
		
		
		/**tds*/
		String[] tdTemps = ieTable.getTemplateTd().split(";");
		List<String> tdKeyList = new ArrayList<String>();
		TreeMap<String,String>  tdMap = new TreeMap<String, String>();
		for (String tds : tdTemps) {
			String key = tds.substring(0,tds.indexOf(",")).split("#")[1].trim();
			String value = tds.substring(0,tds.indexOf(",")).split("#")[0];
			tdMap.put(key, value);
			tdKeyList.add(key);
		}
		
		
		
		/**valuesList*/
		//查询日期集
		Date  sDate = DateUtil.strToDate(searchDate, "yyyy-MM-dd");
		String today = DateUtil.getStringDate(sDate,"yyyy-MM-dd");//查询日期（今）
		conMap.put("date", today);
		String yesterday = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 1),"yyyy-MM-dd");//查询日期（昨）
		String lastWeek = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 7),"yyyy-MM-dd");//查询日期（上周）
		/**表名*/
		conMap.put("tableName",replaceDate(ieTable.getTableName(), today, yesterday, ""));
		conMap.put("tableComment", ieTable.getTableComment());
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(yesterday);dateList.add(lastWeek);
		
		
		List<IETableSearch> speSearchList = new ArrayList<IETableSearch>();//特殊计算
		List<IETableSearch> normalSearchList = new ArrayList<IETableSearch>();//正常计算
		HashMap<String,String> indexTypeMap=new HashMap<String,String>(); //特殊计算指标index:type  type:1百分比,0非百分比
		for(IETableSearch vo : ieTablerSearchList){
			int isSpe = vo.getIsSpe();
			if(isSpe == 1){
				String indexs[] = vo.getIndexs().split("/");
				String evals[] = vo.getSearchSql().split(";");
				for(int i=0; i < indexs.length; i++){
					String index=indexs[i].trim();
					String evalSQl = evals[i];
					indexTypeMap.put(index.split(":")[0], index.split(":")[1]);
					IETableSearch splitedVo = vo.clone();
					splitedVo.setIndexs(index);
					splitedVo.setSearchSql(evalSQl);
					speSearchList.add(splitedVo);
				}
			}else{
				normalSearchList.add(vo);
			}
		}
		
		/**普通数据查询*/
		//key = 行表头(中文名/指标编号);日期  ； value = 值   如     收入/A1;2014-06-01  :  123.4
		Map<String,Object> tempDataMap = new HashMap<String, Object>();
		for(String date : dateList){
			String day = date.replace("-", ""); //默认天
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", ""); //默认月份
			String year =  date.substring(0,date.indexOf("-")); //默认年份
			String lastMonth = DateUtil.getMonthBefore(date, "yyyy-MM-dd", 1); //上月月份
			String lastDay=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 1); //前一天
			String tomorrow=DateUtil.strToStrAfter(date, "yyyy-MM-dd", 1); //后一天
			for(IETableSearch vo : normalSearchList){
				//sql deal
				String sql = vo.getSearchSql();
				try{
				sql = sql.replace("@", "'");
				sql = sql.replace("$", "\"");
				String realDay = day, realYear=year, realMonth=month;//真实查询的年，月
				if(sql.contains("#date0#")){
					realDay = lastDay.replace("-", "");
					realMonth = lastDay.substring(0,lastDay.lastIndexOf("-")).replace("-", "");
					realYear = lastDay.substring(0,lastDay.indexOf("-"));
				}else if(sql.contains("#date2#")){
					realDay = tomorrow.replace("-", "");
					realMonth = tomorrow.substring(0,tomorrow.lastIndexOf("-")).replace("-", "");
					realYear = tomorrow.substring(0,tomorrow.indexOf("-"));
				}
				
				sql = replaceDate(sql, date, lastDay, tomorrow);
				sql = sql.replace("yyyyMM0", lastMonth);
				sql = sql.replace("yyyyMMdd", realDay);
				sql = sql.replace("yyyyMM", realMonth);
				sql = sql.replace("yyyy", realYear);

				//查询指标
				String indexs = vo.getIndexs();
				List<String> nameList = new ArrayList<String>(Arrays.asList(indexs.split("/")));
				List<Map<String,Object>>tempList = comDao.searchDataForIE(sql, nameList, vo.getDbId());
				if(tempList.isEmpty()){
					if(date.equals(today))
						throw new NullPointerException();
					else
						continue;
				}
				Map<String,Object> valueMap = tempList.get(0);
				for(String  colName : nameList){
					if(colName.split(":").length > 1)
						indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
					colName=colName.split(":")[0];
					String rowKeyCname = "";
					//确认该指标的中文名
					for(String tdKey : tdKeyList){
						if(StringUtils.equals(tdKey,colName)){
							rowKeyCname = tdMap.get(tdKey);
							break;
						}
					}
					String key = rowKeyCname+"#"+colName + ";" +  date;
					tempDataMap.put(key, valueMap.get(colName));
				}
				}catch(Exception e){
					Exception cause=new Exception(sql,e);
					throw cause;
				}
			}
		}
		
		
		/** 特殊计算公式处理 */
		//按天把各指标汇总
		Map<String,Map<String,Object>> totalTempMap = new HashMap<String, Map<String,Object>>();
		for(Iterator<String> it = tempDataMap.keySet().iterator() ; it.hasNext();){
			// tempDataMap 结构 收入/A1;2014-06-01  :  123.4
			String key = it.next();
			
			String date = key.split(";")[1];
			String indexName = key.split(";")[0].split("#")[1];
			Object value = tempDataMap.get(key);
			
			Map<String,Object> tempValueMap = new HashMap<String, Object>();
			if(totalTempMap.containsKey(date)){
				tempValueMap = totalTempMap.get(date);
			}
			tempValueMap.put(indexName, value);
			totalTempMap.put(date, tempValueMap);
		}
		
		//代入计算
		for(IETableSearch vo : speSearchList){
			String formula = vo.getSearchSql();
			String indexName = vo.getIndexs().trim().split(":")[0];
			
			for(Iterator<String> it = totalTempMap.keySet().iterator() ; it.hasNext();){
				String date = it.next();
				Map<String,Object> valueMap = totalTempMap.get(date);
				
				String countFormula = formula;
				//替换值  放入计算公式
				for(Iterator<String> it2 = valueMap.keySet().iterator() ; it2.hasNext();){
					String key2 = it2.next();
					Object value = valueMap.get(key2);
					countFormula =replaceKey(countFormula,key2,String.valueOf(value));
				}
				String famount = "--";
				if(!StringUtils.contains(countFormula, "--"))
					famount = String.valueOf(MathEval.eval(countFormula));
				String key = tdMap.get(indexName) + "#" + indexName + ";" + date;
				tempDataMap.put(key, famount);
			}
		}
		
		
		/**计算所有指标的同环比*/
		Map<String,String> hasCompMap = eru.conformDataForIETypeTwo(ieSend,tempDataMap, dateList,indexTypeMap);
		
		/** 整理数据  按照tdKeySet 顺序进行排序  再去除指标代号*/
		List<String> finalData = new ArrayList<String>();
		
		for(String tdIndex : tdKeyList){
			String rowKey = tdMap.get(tdIndex) + "#" + tdIndex;
			String value = hasCompMap.get(rowKey);
			finalData.add( tdMap.get(tdIndex) +";" + value);
		}
		
		
		conMap.put("valuesList",finalData);
		
		content = VelocityTools.render(content, conMap);
		return content;
	}
	
	/**邮件发送  表格内容渲染 - 模板3：分类指标模板
	 */
	public String getTemplateContentThree(IESend ieSend,IETable ieTable,List<IETableSearch> ieTablerSearchList,String searchDate)throws Exception{
		Map<String,Object> conMap = new HashMap<String,Object>();
		String content = MyPathUtil.getTemByPath(sendTemplateMain3);
		/**TH*/
		String[] thTemps = ieTable.getTemplateTh().split(","); //列标
		String cates[] = thTemps[1].split("\\|");
		List<String> thList = new ArrayList<String>(Arrays.asList(cates)); //类别之间是以"|"分开的
		thList.add(0,thTemps[0]);
		
		String[] thTwoTemps = ieTable.getTemplateThTwo().split(","); //分列标
		List<String> thTwoList = new ArrayList<String>(Arrays.asList(thTwoTemps));
		conMap.put("thList",thList);
		conMap.put("thTwoList",thTwoList);
		conMap.put("colTwo",thTwoTemps.length); //分列数
		
		String rowTds[]=ieTable.getTemplateTd().split(";");
		TreeSet<String>  citySet = new TreeSet<String>();		
		Map<String,Integer>  indexMap = new HashMap<String, Integer>(); // index对应的分列表的顺序如A1:0, A3:2, B3:2
		for(String row:rowTds){
			String tmp[]=row.split(",");
			for(int i=0;i<tmp.length;i++){
				indexMap.put(tmp[i], i);
			}
		}
		/**valuesList*/
		//查询日期集
		Date  sDate = DateUtil.strToDate(searchDate, "yyyy-MM-dd");
		String today = DateUtil.getStringDate(sDate,"yyyy-MM-dd");//查询日期（今）
		conMap.put("date", today);
		String yesterday = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 1),"yyyy-MM-dd");//查询日期（昨）
		String lastWeek = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 7),"yyyy-MM-dd");//查询日期（上周）
		/**表名*/
		conMap.put("tableName",replaceDate(ieTable.getTableName(), today, yesterday, ""));
		conMap.put("tableComment", ieTable.getTableComment());
		
		List<String> dateList = new ArrayList<String>();  //dateList只包含要查询的日期，不包含昨日，上周
		dateList.add(today);
//		if(thTwoList.contains("日比"))
			dateList.add(yesterday);
//		if(thTwoList.contains("周比"))
			dateList.add(lastWeek);
		
//		List<IETableSearch> speSearchList = new ArrayList<IETableSearch>();//特殊计算
//		List<IETableSearch> normalSearchList = new ArrayList<IETableSearch>();//正常计算
//		for(IETableSearch vo : ieTablerSearchList){
//			int isSpe = vo.getIsSpe();
//			if(isSpe == 1){
//				speSearchList.add(vo);
//			}else{
//				normalSearchList.add(vo);
//			}
//		}
		
		List<IETableSearch> speSearchList = new ArrayList<IETableSearch>();//特殊计算，跨行
		List<IETableSearch> normalSearchList = new ArrayList<IETableSearch>();//正常计算
		HashMap<String,String> indexTypeMap=new HashMap<String,String>(); //特殊计算指标index:type  type:1百分比,0非百分比
		for(IETableSearch vo : ieTablerSearchList){
			int isSpe = vo.getIsSpe();
			if(isSpe == 1){
				String indexs[] = vo.getIndexs().split("/");
				String evals[] = vo.getSearchSql().split(";");
				for(int i=0; i < indexs.length; i++){
					String index=indexs[i].trim();
					String evalSQl = evals[i];
					indexTypeMap.put(index.split(":")[0], index.split(":")[1]);
					IETableSearch splitedVo = vo.clone();
					splitedVo.setIndexs(index);
					splitedVo.setSearchSql(evalSQl);
					speSearchList.add(splitedVo);
				}
			}else{
				normalSearchList.add(vo);
			}
		}
		
		/**普通数据查询*/
		//key = 行表头;日期  ； value = 指标：值
		Map<String,Map<String,Object>> tempDataMap = new HashMap<String, Map<String,Object>>();
		boolean hasBeginDate=false;
		for(String date : dateList){
			String day = date.replace("-", ""); //默认天
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", ""); //默认月份
			String year =  date.substring(0,date.indexOf("-")); //默认年份
			String lastMonth = DateUtil.getMonthBefore(date, "yyyy-MM-dd", 1); //上月月份
			String lastDay=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 1); //前一天
			String tomorrow=DateUtil.strToStrAfter(date, "yyyy-MM-dd", 1); //后一天
			for(IETableSearch vo : normalSearchList){
				//sql deal
				String sql = vo.getSearchSql();
				try{
				sql = sql.replace("@", "'");
				sql = sql.replace("$", "\"");
				String realDay = day, realYear=year, realMonth=month;//真实查询的年，月
				if(sql.contains("#date0#")){
					realDay = lastDay.replace("-", "");
					realMonth = lastDay.substring(0,lastDay.lastIndexOf("-")).replace("-", "");
					realYear = lastDay.substring(0,lastDay.indexOf("-"));
				}else if(sql.contains("#date2#")){
					realDay = tomorrow.replace("-", "");
					realMonth = tomorrow.substring(0,tomorrow.lastIndexOf("-")).replace("-", "");
					realYear = tomorrow.substring(0,tomorrow.indexOf("-"));
				}
				
				
				sql = replaceDate(sql, date, lastDay, tomorrow);
				//查询指标
				String indexs = vo.getIndexs();
				List<String> nameList = new ArrayList<String>(Arrays.asList(indexs.split("/")));
				//如果SQL是group by类型  则要在nameList第一位加入groupBy 进行占位
				if(StringUtils.containsIgnoreCase(sql,"group")){
					nameList.add(0, "city");
					nameList.add(1, "cate");
				}
				List<Map<String,Object>>tempList = new ArrayList<Map<String,Object>>();
				if(sql.contains("#beginDate#")){
					hasBeginDate=true;
					String beginDate=DateUtil.strToStrBefore(today, "yyyy-MM-dd", 7);
					String endDate=today;
					if(sql.contains("yyyyMMdd")){	//天表
						List<String> daySearchList = DateUtil.getDayList(beginDate, endDate);
						endDate = DateUtil.strToStrAfter(endDate, "yyyy-MM-dd", 1);	//SQL中配置的是stat_date<endDate, 所以向后推一天
						for(String curday : daySearchList){
							String newsql = sql.replace("yyyyMMdd", curday);
							newsql=newsql.replace("#beginDate#", beginDate).replace("#endDate#", endDate);
							tempList.addAll(comDao.searchDataForIE(newsql, nameList, vo.getDbId()));
						}
					}
					else{		//月表
						List<String> monthSearchList = DateUtil.subSearchDate(beginDate, endDate);
						for(String con : monthSearchList){
							String[] conArrays = con.split(";");
							String curMonth = conArrays[0];
							String start = conArrays[1];
							String end = conArrays[2];
							String newsql = sql.replace("yyyyMM0", lastMonth);
							newsql = newsql.replace("yyyyMM", curMonth);
							newsql = newsql.replace("yyyy", curMonth.substring(0,4));
							newsql=newsql.replace("#beginDate#", start).replace("#endDate#", end);
							tempList.addAll(comDao.searchDataForIE(newsql, nameList, vo.getDbId()));
						}
					}
				}else{
					sql = sql.replace("yyyyMM0", lastMonth);
					sql = sql.replace("yyyyMMdd", realDay);
					sql = sql.replace("yyyyMM", realMonth);
					sql = sql.replace("yyyy",realYear); 
					tempList = comDao.searchDataForIE(sql, nameList, vo.getDbId());
//					System.out.println(sql);
				}
//				System.out.println("results size: "+tempList.size());
				if(tempList.isEmpty())
					throw new NullPointerException();
				if(tempList.size() > 0){ //group by sql deal
					//处理结果集，每个valueMap存储一行的指标数据
					for (Map<String, Object> valueMap : tempList) {
						String city=valueMap.get("city").toString();
						citySet.add(city);
						String rowKey = valueMap.get("city") + ";" + valueMap.get("cate") + ";" + date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						
						for(String colName : nameList){
							if(colName.split(":").length > 1)
								indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
							colName=colName.split(":")[0];
							if("city".equals(colName)||"cate".equals(colName)){
								continue;
							}
							Object colValue = valueMap.get(colName);
							temp.put(colName, colValue);
						}
						evalValue(speSearchList, temp); //普通计算，如果该行有需要计算的指标
						tempDataMap.put(rowKey, temp);
					}
				}
				}catch(Exception e){
					throw new Exception(sql,e);
				}
			}
			if(hasBeginDate)
				break;
		}	

		//拼数据
		List<List<String>> finalDatas=new ArrayList<List<String>>();
//		for (String date : dateList) { //日期，只包含查询日期今天，暂时留着
			for (String city : citySet) { //三层循环，城市，类别，指标三个维度确定一个值
				List<String> rowdata=new ArrayList<String>();
				String[] tmpData=new String[1+cates.length*thTwoList.size()];
				Arrays.fill(tmpData, "0");
				tmpData[0]=city;
				for ( int i=0;i<cates.length;i++) {
					String cate=cates[i];
					String rowKey=city+";"+cate+";";
//					System.out.println(rowKey);
					Map<String, Object> valueMap=tempDataMap.get(rowKey+today);
					Map<String, Object> yesValueMap=tempDataMap.get(rowKey+yesterday);
					Map<String, Object> lastWeekValueMap=tempDataMap.get(rowKey+lastWeek);
//					if(hasBeginDate){
//						yesValueMap=tempDataMap.get(DateUtil.strToStrBefore(city, "yyyy-MM-dd", 1)+";"+cate+";"+today);
//						lastWeekValueMap=tempDataMap.get(DateUtil.strToStrBefore(city, "yyyy-MM-dd", 7)+";"+cate+";"+today);
//					}
					if(valueMap == null || valueMap.isEmpty()){ //该类别没有数据
						continue;
					}
					else{
					Set<String> keySet=valueMap.keySet();
					for(String column:keySet){  //column为指标如A1，B2
						int columnIndex=indexMap.get(column);
						int rowIndex=i*thTwoList.size()+columnIndex; //计算该指标在该行的位置
						String type=indexTypeMap.get(column);
						String value=eru.conformDataForIETypeThree(valueMap.get(column),type);
						if("1".equals(type)){
							if(thTwoList.get(columnIndex-1).equals("环比"))
								value=vt.trans2RateWithColor(value);
							else
								 value=vt.comRate(value);
						}
						tmpData[rowIndex]=value;
						String yesValue="--";
						String yesRate="--";
						String lastWeekValue="--";
						String lastWeekRate ="--";
						//昨日
						if(yesValueMap!=null && yesValueMap.containsKey(column)){
							ieSend.setTdNum(ieSend.getTdNum()+1);
							yesValue = eru.conformDataForIETypeThree(yesValueMap.get(column),type);
							yesRate = vt.compRate2(valueMap.get(column),yesValueMap.get(column));
							double rate=vt.compRate3(valueMap.get(column), yesValueMap.get(column));
							if(Math.abs(rate)>ieSend.getThreshold()){
								ieSend.setAbnormalTd(ieSend.getAbnormalTd()+1);
							}
						}
						//上周
						if(lastWeekValueMap!=null && lastWeekValueMap.containsKey(column)){
							ieSend.setTdNum(ieSend.getTdNum()+1);
							lastWeekValue = eru.conformDataForIETypeThree(lastWeekValueMap.get(column),type);
							lastWeekRate = vt.compRate2(valueMap.get(column),lastWeekValueMap.get(column));
							double rate = vt.compRate3(valueMap.get(column), lastWeekValueMap.get(column));
							if(Math.abs(rate)>ieSend.getThreshold()){
								ieSend.setAbnormalTd(ieSend.getAbnormalTd()+1);
							}
						}
						StringBuffer newValue = new StringBuffer();
						newValue.append(value+";").append(yesValue+";").append(yesRate+";").append(lastWeekValue+";").append(lastWeekRate+";");
						String row = newValue.toString();
						for(int k=0;k<thIndexMap.size() && columnIndex < thTwoList.size();k++){    //看该指标之后有无昨日，日比，上周，周比
							boolean hasRate=false;
							String th = thTwoList.get(columnIndex);
							if(thIndexMap.containsKey(th)){
								hasRate=true;
								int index = thIndexMap.get(th);
								tmpData[++rowIndex] =  row.split(";")[index];
							}
							if(hasRate==false)
								break;
							columnIndex++;
						}
					}
					}
				}
				rowdata=Arrays.asList(tmpData);
				finalDatas.add(rowdata); //一行数据完成
			}
//		}
		
		/**默认按照第一列指标值升序排序*/
		conMap.put("valuesList",finalDatas);
		
		content = VelocityTools.render(content, conMap);
		return content;
	}


	/**邮件发送  表格内容渲染 - 模板5 ：按行分类模板
	 * 参见黄页运营日报里的城市&品类数据，以及简历日报中的简历下载模块
	 */ 
	public String getTemplateContentFive(IESend ieSend,IETable ieTable,List<IETableSearch> ieTablerSearchList,String searchDate)throws Exception{
		Map<String,Object> conMap = new HashMap<String,Object>();
		String content = MyPathUtil.getTemByPath(sendTemplateMain5);
		/**TH*/
		String[] thTemps = ieTable.getTemplateTh().split(",");
		List<String> thList = new ArrayList<String>(Arrays.asList(thTemps));
		conMap.put("thList",thList);
		/**TH2*/
		String templateThTwo=ieTable.getTemplateThTwo();
		List<String> rowHeadList = new ArrayList<String>(); //行头
		List<String> thTwoList = new ArrayList<String>(); //分行头，Arrays.asList(templateThTwo.replace(";",",").split(","))
		List<String> rowHeadList_Tmp = new ArrayList<String>(); //行头,临时借用
		List<String> thTwoList_Tmp = new ArrayList<String>(); //分行头,临时借用
		List<Integer> rowspanList = new ArrayList<Integer>(); //每行的合并数rowspan=...
		/**tds*/
		String[] tdTemps = ieTable.getTemplateTd().split(";");
		Map<String,String>  tdMap = new HashMap<String, String>(); //指标：行键，如A1：北京_精准
		Map<String,String>  rowTdMap = new HashMap<String, String>(); //指标：行索引 如A1：A1,null,B1,null,null 
		String[] thTwos=templateThTwo.split(";");   //templateThTwo存的是第二列的内容。
		int n=0;
		int rowNum=0;
		HashMap<Integer,Integer> numMap = new HashMap<Integer,Integer>();	//行号：改行隶属的带有rowspan属性的行号
		for(int i=0;i<tdTemps.length;i++){
			
			numMap.put(i,rowNum);
			if(i == rowNum){
				int rowspan = thTwos[n++].split(",").length;
				rowNum=rowNum + rowspan;
				if(!tdTemps[i].contains("groupBy"))
					rowspanList.add(rowspan);
			}
		}
		
		String rowHead="";
		for(int j=0;j<tdTemps.length;j++){
			String tds = tdTemps[j];
			int index=tds.indexOf(",");
			String key= tds.substring(index+1); //一行指标集,逗号分隔
			String value = tds.substring(0,index); //第二列的行头
			if(j == numMap.get(j)){  //该行为较长的一行，带有rowspan属性
				rowHead = tds.substring(0,index);	//第一列的行头
				value = key.substring(0,key.indexOf(","));
				key = key.substring(key.indexOf(",")+1);
				if(!tdTemps[j].contains("groupBy")){
					rowHeadList.add(rowHead);
					thTwoList.add(value);
				}
			}else{
				thTwoList.add(value);
			}
			String val = rowHead+"_"+value;
			for(String s: key.split(",")){
				tdMap.put(s, val);
				rowTdMap.put(s,key);
			}
		}
//		Set<String> tdKeySet = tdMap.keySet();

		/**valuesList*/
		//查询日期集
		Date  sDate = DateUtil.strToDate(searchDate, "yyyy-MM-dd");
		String today = DateUtil.getStringDate(sDate,"yyyy-MM-dd");//查询日期（今）
		conMap.put("date", today);
		String yesterday = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 1),"yyyy-MM-dd");//查询日期（昨）
		String lastWeek = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 7),"yyyy-MM-dd");//查询日期（上周）
		/**表名*/
		conMap.put("tableName",replaceDate(ieTable.getTableName(), today, yesterday, ""));
		conMap.put("tableComment", ieTable.getTableComment());
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(yesterday);dateList.add(lastWeek);
		
		
		List<IETableSearch> speSearchList = new ArrayList<IETableSearch>();//特殊计算，跨行
		List<IETableSearch> innerSpeList = new ArrayList<IETableSearch>();//特殊计算,行内
		List<IETableSearch> normalSearchList = new ArrayList<IETableSearch>();//正常计算
		HashMap<String,String> indexTypeMap=new HashMap<String,String>(); //特殊计算指标index:type  type:1百分比,0非百分比
		for(IETableSearch vo : ieTablerSearchList){
			int isSpe = vo.getIsSpe();
			if(isSpe == 1){
				String indexs[] = vo.getIndexs().split("/");
				String evals[] = vo.getSearchSql().split(";");
				for(int i=0; i < indexs.length; i++){
					String index=indexs[i].trim();
					String evalSQl = evals[i];
					indexTypeMap.put(index.split(":")[0], index.split(":")[1]);
					String rowIndexs=rowTdMap.get(index.split(":")[0]);
					Set<String> rowTdSet = new HashSet<String>();
					rowTdSet.addAll(Arrays.asList(rowIndexs.replace("null", "").split(",")));
					IETableSearch splitedVo = vo.clone();
					splitedVo.setIndexs(index);
					splitedVo.setSearchSql(evalSQl);
					if(MathEval.checkIndexBySelfRow(evalSQl, rowTdSet)){
						innerSpeList.add(splitedVo);
					}else{
						speSearchList.add(splitedVo);
					}
				}
			}else{
				normalSearchList.add(vo);
			}
		}
		boolean hasGroupBy=false;
//		Set<String> rowHeadSet=new HashSet<String>();
//		Set<String> thTwoSet=new HashSet<String>();
		/**普通数据查询*/
		//key = 行表头;日期  ； value = 指标：值
		Map<String,Map<String,Object>> tempDataMap = new HashMap<String, Map<String,Object>>();
		for(String date : dateList){
			String day = date.replace("-", ""); //默认天
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", ""); //默认月份
			String year =  date.substring(0,date.indexOf("-")); //默认年份
			String lastMonth = DateUtil.getMonthBefore(date, "yyyy-MM-dd", 1); //上月月份
			String lastDay=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 1); //前一天
			String tomorrow=DateUtil.strToStrAfter(date, "yyyy-MM-dd", 1); //后一天
			for(IETableSearch vo : normalSearchList){
				//sql deal
				String sql = vo.getSearchSql();
				try{
				sql = sql.replace("@", "'");
				sql = sql.replace("$", "\"");
				String realDay = day, realYear=year, realMonth=month;//真实查询的年，月
				if(sql.contains("#date0#")){
					realDay = lastDay.replace("-", "");
					realMonth = lastDay.substring(0,lastDay.lastIndexOf("-")).replace("-", "");
					realYear = lastDay.substring(0,lastDay.indexOf("-"));
				}else if(sql.contains("#date2#")){
					realDay = tomorrow.replace("-", "");
					realMonth = tomorrow.substring(0,tomorrow.lastIndexOf("-")).replace("-", "");
					realYear = tomorrow.substring(0,tomorrow.indexOf("-"));
				}
				
				sql = replaceDate(sql, date, lastDay, tomorrow);
				sql = sql.replace("yyyyMM0", lastMonth);
				sql = sql.replace("yyyyMMdd", realDay);
				sql = sql.replace("yyyyMM", realMonth);
				sql = sql.replace("yyyy", realYear);
				//查询指标
				String indexs = vo.getIndexs();
				List<String> nameList = new ArrayList<String>(Arrays.asList(indexs.split("/")));
				//如果SQL是group by类型  则要在nameList第一位加入groupBy 进行占位
				if(StringUtils.containsIgnoreCase(tdMap.get(nameList.get(0).split(":")[0]),"group")){
					nameList.add(0, "groupBy1");
					nameList.add(1, "groupBy2");
					hasGroupBy=true;
				}
//				if(StringUtils.containsIgnoreCase(sql,"group")){
//					hasGroupBy=true;
//					nameList.add(0, "groupBy1");
//					nameList.add(1, "groupBy2");
//				}
				
				List<Map<String,Object>>tempList = comDao.searchDataForIE(sql, nameList, vo.getDbId());
				if(tempList.isEmpty()){
					if(date.equals(today))
						throw new NullPointerException();
					else
						continue;
				}
				if(nameList.contains("groupBy1")){  //group by sql deal
					for (Map<String, Object> valueMap : tempList) {
						if(date.equals(today)){	//查询日期为今日
							if(!rowHeadList_Tmp.contains(valueMap.get("groupBy1").toString())){	//行头未出现过
								rowHeadList_Tmp.add(valueMap.get("groupBy1").toString());
							}
							if(!thTwoList_Tmp.contains(valueMap.get("groupBy2").toString())){	//分行头为出现过，加入，最后作笛卡尔积
								thTwoList_Tmp.add(valueMap.get("groupBy2").toString());
							}
						}
//						rowHeadSet.add(valueMap.get("groupBy1").toString());
//						thTwoSet.add(valueMap.get("groupBy2").toString());
						String rowKey = valueMap.get("groupBy1") + "_"+valueMap.get("groupBy2")+";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						
						for(String colName : nameList){
							if(colName.split(":").length > 1)
								indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
							colName=colName.split(":")[0];
							if(colName.contains("groupBy")){
								continue;
							}
							Object colValue = valueMap.get(colName);
							temp.put(colName, colValue);
						}
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}else { //正常
					Map<String,Object> valueMap = tempList.get(0);
					for(String  colName : nameList){
						if(colName.split(":").length > 1)
							indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
						colName=colName.split(":")[0];
						if(colName.contains("groupBy")){
							continue;
						}
						String rowKey = "";
						//确认该指标归属哪一行
						rowKey = tdMap.get(colName);
						rowKey = rowKey + ";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						temp.put(colName, valueMap.get(colName));
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}
				}catch(Exception e){
					throw new Exception(sql,e);
				}
			}
		}
		
		//日期：指标：值 ，如'2014-07-31':{A1:5400, B2:23.5}
		Map<String,Map<String,Object>> indexDataMap =  new HashMap<String, Map<String,Object>>(); 
		for(Iterator<String> it = tempDataMap.keySet().iterator(); it.hasNext();){
			String key = it.next();
			Map<String,Object> valueMap = tempDataMap.get(key);
			String dateKey = key.split(";")[1]; 
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(dateKey)){
				sumValueMap = indexDataMap.get(dateKey);
			}
			
			for(Iterator<String> it3 = valueMap.keySet().iterator(); it3.hasNext();){
				String index = it3.next();
				double value = 0;
				String strVal=valueMap.get(index).toString();
				try{
					 value = Double.valueOf(strVal);
				}catch(Exception e){
					sumValueMap.put(index, 0);
					continue;
				}
				if(sumValueMap.containsKey(index)){
					value = value + Double.valueOf(sumValueMap.get(index).toString());
				}
				sumValueMap.put(index, value);
			}
			indexDataMap.put(dateKey, sumValueMap);
		}
		/**特殊计算公式指标处理 */
		for(String date:dateList){
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(date)){
				sumValueMap = indexDataMap.get(date);
			}
			List<IETableSearch> speList=new ArrayList<IETableSearch>();
			for(IETableSearch vo : speSearchList){
				String index = vo.getIndexs().trim().split(":")[0];
				String formula = vo.getSearchSql();
				if(MathEval.checkIndexBySelfRow(formula, sumValueMap.keySet())){
					//检查该指标的计算公式是否仅依赖已有指标
					String countFormula = formula;
					//替换值  放入计算公式
					for(Iterator<String> it2 = sumValueMap.keySet().iterator() ; it2.hasNext();){
						String key2 = it2.next();
						Object value = sumValueMap.get(key2);
						countFormula =replaceKey(countFormula,key2,String.valueOf(value));
					}
					String famount = "--";
					if(!StringUtils.contains(countFormula, "--"))
						famount = String.valueOf(MathEval.eval(countFormula));
					String rowKey=tdMap.get(index)+";"+date;
					Map<String,Object> valueMap = tempDataMap.get(rowKey);
					if(valueMap==null)
						valueMap = new HashMap<String, Object>();
					valueMap.put(index, famount);
					tempDataMap.put(rowKey, valueMap);
				}else{
					speList.add(vo);
				}
				if(speList.size()>0){
					for(IETableSearch v:speList)
						IESendTimerTask.getLogger().error(v.getIndexs()+": "+v.getSearchSql());
				}
		  }
		}
		
		/**计算所有指标的同环比*/
		Map<String,Map<String,String>> hasCompMap = eru.conformDataForIETypeOne(ieSend,tempDataMap,indexTypeMap,dateList);
		
		/**根据TDS来确定指标顺序及同环比是否展示*/
		Map<String,String> dataMap=new HashMap<String,String>();
//		List<String> finalData = new ArrayList<String>();
		for(Iterator<String> it = hasCompMap.keySet().iterator(); it.hasNext();){
			String rowKey = it.next();
			
			Map<String,String> valuesMap = hasCompMap.get(rowKey);
			String showIndexs = ""; // like B1,null,null,B2,null,null,B3,null
			
			//抽取该列第一个指标 判断该指标所属的正常列排序是什么
			for(Iterator<String> it2 = valuesMap.keySet().iterator(); it2.hasNext();){
				String index = it2.next();
				if(rowTdMap.containsKey(index)){
						showIndexs = rowTdMap.get(index);
						break;
				}
			}
			
			//结合 th和td 取出展示出来的指标及同比环比
			StringBuffer tempValue = new StringBuffer();
//			tempValue.append(rowKey+";");
			
			String[] showIndexTemp = showIndexs.split(",");
			String lastIndex = "";
			for (int i = 0 ; i< showIndexTemp.length; i++) {
				String showIndex = showIndexTemp[i];
				String thValue = thTemps[i+1];
				String appValue =  "";
				if(!"null".equals(showIndex)){ //正常值
					String value=valuesMap.get(showIndex);
					if(value==null)
						appValue="--";
					else
						appValue = value.split(";")[0];
					String type=indexTypeMap.get(showIndex);
					if("1".equals(type)){
						if("环比".equals(thValue))
							appValue=vt.trans2RateWithColor(appValue);
						else
							appValue=vt.comRate(appValue);
					}
					lastIndex = showIndex;
				}else{ //同比、周比
					int index = thIndexMap.get(thValue);
					String type=indexTypeMap.get(lastIndex);
					if(index ==1 || index == 3){	//昨日， 上周
						if(!valuesMap.containsKey(lastIndex))
							appValue = "--";
						else
							appValue = valuesMap.get(lastIndex).split(";")[index];
						if("1".equals(type)){ //如果是百分比类型
							appValue=vt.comRate(appValue);
						}
					}else if(index ==2 || index == 4){
						if(!valuesMap.containsKey(lastIndex))
							appValue = "0.00%";    
						else
							appValue = valuesMap.get(lastIndex).split(";")[index];
					}
					
				}
				tempValue.append(appValue+";");
			}
			dataMap.put(rowKey, tempValue.toString());
//			finalData.add(tempValue.toString());
		}
		
		if(hasGroupBy){ //分组groupBy1,groupBy2,参见黄页运营日报的城市&品类数据。
			Set<String> rowKeySet = new HashSet<String>();
			for(String head1: rowHeadList_Tmp){
				for(String head2: thTwoList_Tmp){
					String keyString = head1+"_"+head2;
					if(!rowKeySet.contains(keyString) && dataMap.containsKey(keyString)){
						rowKeySet.add(keyString);
						rowHeadList.add(head1);
						thTwoList.add(head2);
						rowspanList.add(1);
					}
				}
			}
		}
		
		/**默认按照第一列指标值降序排序*/
//		finalData = SortUtil.sortIEListByFileName(finalData, 1, 2);
		conMap.put("rowHeadList",rowHeadList);
		conMap.put("thTwoList",thTwoList);
		conMap.put("rowspanList",rowspanList);
		conMap.put("valuesMap",dataMap);
		
		content = VelocityTools.render(content, conMap);
		return content;
	}


	/**  模板6 ：按行按列分类模板
	 */ 
	public String getTemplateContentSix(IESend ieSend,IETable ieTable,List<IETableSearch> ieTablerSearchList,String searchDate)throws Exception{
		Map<String,Object> conMap = new HashMap<String,Object>();
		String content = MyPathUtil.getTemByPath(sendTemplateMain6);

		/**TH2*/
		String templateThTwo=ieTable.getTemplateThTwo();
		List<String> rowHeadList = new ArrayList<String>(); //行头
		List<String> thTwoList = new ArrayList<String>(); //分行头，Arrays.asList(templateThTwo.replace(";",",").split(","))
		List<String> rowHeadList_Tmp = new ArrayList<String>(); //行头,临时借用
		List<String> thTwoList_Tmp = new ArrayList<String>(); //分行头,临时借用
		List<Integer> rowspanList = new ArrayList<Integer>(); //每行的合并数rowspan=...
		/**tds*/
		String[] tdTemps = ieTable.getTemplateTd().split(";");
		Map<String,String>  tdMap = new HashMap<String, String>(); //指标：行键，如A1：北京_精准
		Map<String,String>  rowTdMap = new HashMap<String, String>(); //指标：行索引 如A1：A1,null,B1,null,null 
		String[] rowThTwo=templateThTwo.split("!!")[1].split(";");   //templateThTwo存的是 “分列头!!分行头”。
		
		
		/**TH*/
		String[] ths = ieTable.getTemplateTh().split(",");
		List<String> thList = new ArrayList<String>(Arrays.asList(ths));
		conMap.put("thList",thList);
		
		//列组合
		String cloThTwoStr = templateThTwo.split("!!")[0];
		ArrayList<Integer> colsSpanList=new ArrayList<Integer>();
		String[] colGroup=cloThTwoStr.split(";");
		for(String s:colGroup){
			colsSpanList.add(s.split(",").length);
		}
		conMap.put("colspan",colsSpanList);
		
		String[] colThTwo = cloThTwoStr.replace(";", ",").split(",");
		List<String> colThTwoList = new ArrayList<String>(Arrays.asList(colThTwo));
		conMap.put("colThTwoList",colThTwoList);
		
		
		int n=0;
		int rowNum=0;
		HashMap<Integer,Integer> numMap = new HashMap<Integer,Integer>();	//行号：改行隶属的带有rowspan属性的行号
		for(int i=0;i<tdTemps.length;i++){
			
			numMap.put(i,rowNum);
			if(i == rowNum){
				int rowspan = rowThTwo[n++].split(",").length;
				rowNum=rowNum + rowspan;
				if(!tdTemps[i].contains("groupBy"))
					rowspanList.add(rowspan);
			}
		}
		
		String rowHead="";
		for(int j=0;j<tdTemps.length;j++){
			String tds = tdTemps[j];
			int index=tds.indexOf(",");
			String key= tds.substring(index+1); //一行指标集,逗号分隔
			String value = tds.substring(0,index); //第二列的行头
			if(j == numMap.get(j)){  //该行为较长的一行，带有rowspan属性
				rowHead = tds.substring(0,index);	//第一列的行头
				value = key.substring(0,key.indexOf(","));
				key = key.substring(key.indexOf(",")+1);
				if(!tdTemps[j].contains("groupBy")){
					rowHeadList.add(rowHead);
					thTwoList.add(value);
				}
			}else{
				thTwoList.add(value);
			}
			String val = rowHead+"_"+value;
			for(String s: key.split(",")){
				tdMap.put(s, val);
				rowTdMap.put(s,key);
			}
		}
//		Set<String> tdKeySet = tdMap.keySet();

		/**valuesList*/
		//查询日期集
		Date  sDate = DateUtil.strToDate(searchDate, "yyyy-MM-dd");
		String today = DateUtil.getStringDate(sDate,"yyyy-MM-dd");//查询日期（今）
		conMap.put("date", today);
		String yesterday = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 1),"yyyy-MM-dd");//查询日期（昨）
		String lastWeek = DateUtil.getStringDate(DateUtil.getDateBefore(sDate, 7),"yyyy-MM-dd");//查询日期（上周）
		/**表名*/
		conMap.put("tableName",replaceDate(ieTable.getTableName(), today, yesterday, ""));
		conMap.put("tableComment", ieTable.getTableComment());
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(today);dateList.add(yesterday);dateList.add(lastWeek);
		
		
		List<IETableSearch> speSearchList = new ArrayList<IETableSearch>();//特殊计算，跨行
		List<IETableSearch> innerSpeList = new ArrayList<IETableSearch>();//特殊计算,行内
		List<IETableSearch> normalSearchList = new ArrayList<IETableSearch>();//正常计算
		HashMap<String,String> indexTypeMap=new HashMap<String,String>(); //特殊计算指标index:type  type:1百分比,0非百分比
		for(IETableSearch vo : ieTablerSearchList){
			int isSpe = vo.getIsSpe();
			if(isSpe == 1){
				String indexs[] = vo.getIndexs().split("/");
				String evals[] = vo.getSearchSql().split(";");
				for(int i=0; i < indexs.length; i++){
					String index=indexs[i].trim();
					String evalSQl = evals[i];
					indexTypeMap.put(index.split(":")[0], index.split(":")[1]);
					String rowIndexs=rowTdMap.get(index.split(":")[0]);
					Set<String> rowTdSet = new HashSet<String>();
					rowTdSet.addAll(Arrays.asList(rowIndexs.replace("null", "").split(",")));
					IETableSearch splitedVo = vo.clone();
					splitedVo.setIndexs(index);
					splitedVo.setSearchSql(evalSQl);
					if(MathEval.checkIndexBySelfRow(evalSQl, rowTdSet)){
						innerSpeList.add(splitedVo);
					}else{
						speSearchList.add(splitedVo);
					}
				}
			}else{
				normalSearchList.add(vo);
			}
		}
		boolean hasGroupBy=false;
//		Set<String> rowHeadSet=new HashSet<String>();
//		Set<String> thTwoSet=new HashSet<String>();
		/**普通数据查询*/
		//key = 行表头;日期  ； value = 指标：值
		Map<String,Map<String,Object>> tempDataMap = new HashMap<String, Map<String,Object>>();
		for(String date : dateList){
			String day = date.replace("-", ""); //默认天
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", ""); //默认月份
			String year =  date.substring(0,date.indexOf("-")); //默认年份
			String lastMonth = DateUtil.getMonthBefore(date, "yyyy-MM-dd", 1); //上月月份
			String lastDay=DateUtil.strToStrBefore(date, "yyyy-MM-dd", 1); //前一天
			String tomorrow=DateUtil.strToStrAfter(date, "yyyy-MM-dd", 1); //后一天
			for(IETableSearch vo : normalSearchList){
				//sql deal
				String sql = vo.getSearchSql();
				try{
				sql = sql.replace("@", "'");
				sql = sql.replace("$", "\"");
				String realDay = day, realYear=year, realMonth=month;//真实查询的年，月
				if(sql.contains("#date0#")){
					realDay = lastDay.replace("-", "");
					realMonth = lastDay.substring(0,lastDay.lastIndexOf("-")).replace("-", "");
					realYear = lastDay.substring(0,lastDay.indexOf("-"));
				}else if(sql.contains("#date2#")){
					realDay = tomorrow.replace("-", "");
					realMonth = tomorrow.substring(0,tomorrow.lastIndexOf("-")).replace("-", "");
					realYear = tomorrow.substring(0,tomorrow.indexOf("-"));
				}
				
				sql = replaceDate(sql, date, lastDay, tomorrow);
				sql = sql.replace("yyyyMM0", lastMonth);
				sql = sql.replace("yyyyMMdd", realDay);
				sql = sql.replace("yyyyMM", realMonth);
				sql = sql.replace("yyyy", realYear);
				//查询指标
				String indexs = vo.getIndexs();
				List<String> nameList = new ArrayList<String>(Arrays.asList(indexs.split("/")));
				//如果SQL是group by类型  则要在nameList第一位加入groupBy 进行占位
				if(StringUtils.containsIgnoreCase(tdMap.get(nameList.get(0).split(":")[0]),"group")){
					nameList.add(0, "groupBy1");
					nameList.add(1, "groupBy2");
					hasGroupBy=true;
				}
				
				List<Map<String,Object>>tempList = comDao.searchDataForIE(sql, nameList, vo.getDbId());
				if(tempList.isEmpty()){
					if(date.equals(today))
						throw new NullPointerException();
					else
						continue;
				}
				if(nameList.contains("groupBy1")){  //group by sql deal
					for (Map<String, Object> valueMap : tempList) {
						if(date.equals(today)){	//查询日期为今日
							if(!rowHeadList_Tmp.contains(valueMap.get("groupBy1").toString())){	//行头未出现过
								rowHeadList_Tmp.add(valueMap.get("groupBy1").toString());
							}
							if(!thTwoList_Tmp.contains(valueMap.get("groupBy2").toString())){	//分行头未出现过，加入，最后作笛卡尔积
								thTwoList_Tmp.add(valueMap.get("groupBy2").toString());
							}
						}
//						rowHeadSet.add(valueMap.get("groupBy1").toString());
//						thTwoSet.add(valueMap.get("groupBy2").toString());
						String rowKey = valueMap.get("groupBy1") + "_"+valueMap.get("groupBy2")+";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						
						for(String colName : nameList){
							if(colName.split(":").length > 1)
								indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
							colName=colName.split(":")[0];
							if(colName.contains("groupBy")){
								continue;
							}
							Object colValue = valueMap.get(colName);
							temp.put(colName, colValue);
						}
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}else { //正常
					Map<String,Object> valueMap = tempList.get(0);
					for(String  colName : nameList){
						if(colName.split(":").length > 1)
							indexTypeMap.put(colName.split(":")[0], colName.split(":")[1]);
						colName=colName.split(":")[0];
						if(colName.contains("groupBy")){
							continue;
						}
						String rowKey = "";
						//确认该指标归属哪一行
						rowKey = tdMap.get(colName);
						rowKey = rowKey + ";" +  date;
						Map<String,Object> temp = new HashMap<String, Object>();
						if(tempDataMap.containsKey(rowKey)){
							temp = tempDataMap.get(rowKey);
						}
						temp.put(colName, valueMap.get(colName));
						evalValue(innerSpeList, temp);
						tempDataMap.put(rowKey, temp);
					}
				}
				}catch(Exception e){
					throw new Exception(sql,e);
				}
			}
		}
		
		//日期：指标：值 ，如'2014-07-31':{A1:5400, B2:23.5}
		Map<String,Map<String,Object>> indexDataMap =  new HashMap<String, Map<String,Object>>(); 
		for(Iterator<String> it = tempDataMap.keySet().iterator(); it.hasNext();){
			String key = it.next();
			Map<String,Object> valueMap = tempDataMap.get(key);
			String dateKey = key.split(";")[1]; 
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(dateKey)){
				sumValueMap = indexDataMap.get(dateKey);
			}
			
			for(Iterator<String> it3 = valueMap.keySet().iterator(); it3.hasNext();){
				String index = it3.next();
				double value = 0;
				String strVal=valueMap.get(index).toString();
				try{
					 
					if(strVal.equals("--"))
						value=0;
					else value = Double.valueOf(strVal);
				}catch(Exception e){
					sumValueMap.put(index, strVal);
					continue;
				}
				if(sumValueMap.containsKey(index)){
					value = value + Double.valueOf(sumValueMap.get(index).toString());
				}
				sumValueMap.put(index, value);
			}
			indexDataMap.put(dateKey, sumValueMap);
		}
		/**特殊计算公式指标处理 */
		for(String date:dateList){
			Map<String,Object> sumValueMap = new HashMap<String, Object>();
			if(indexDataMap.containsKey(date)){
				sumValueMap = indexDataMap.get(date);
			}
			List<IETableSearch> speList=new ArrayList<IETableSearch>();
			for(IETableSearch vo : speSearchList){
				String index = vo.getIndexs().trim().split(":")[0];
				String formula = vo.getSearchSql();
				if(MathEval.checkIndexBySelfRow(formula, sumValueMap.keySet())){
					//检查该指标的计算公式是否仅依赖已有指标
					String countFormula = formula;
					//替换值  放入计算公式
					for(Iterator<String> it2 = sumValueMap.keySet().iterator() ; it2.hasNext();){
						String key2 = it2.next();
						Object value = sumValueMap.get(key2);
						countFormula =replaceKey(countFormula,key2,String.valueOf(value));
					}
					String famount = "--";
					if(!StringUtils.contains(countFormula, "--"))
						famount = String.valueOf(MathEval.eval(countFormula));
					String rowKey=tdMap.get(index)+";"+date;
					Map<String,Object> valueMap = tempDataMap.get(rowKey);
					if(valueMap==null)
						valueMap = new HashMap<String, Object>();
					valueMap.put(index, famount);
					tempDataMap.put(rowKey, valueMap);
				}else{
					speList.add(vo);
				}
				if(speList.size()>0){
					for(IETableSearch v:speList)
						IESendTimerTask.getLogger().error(v.getIndexs()+": "+v.getSearchSql());
				}
		  }
		}
		
		/**计算所有指标的同环比*/
		Map<String,Map<String,String>> hasCompMap = eru.conformDataForIETypeOne(ieSend,tempDataMap,indexTypeMap,dateList);
		
		/**根据TDS来确定指标顺序及同环比是否展示*/
		Map<String,String> dataMap=new HashMap<String,String>();
//		List<String> finalData = new ArrayList<String>();
		for(Iterator<String> it = hasCompMap.keySet().iterator(); it.hasNext();){
			String rowKey = it.next();
			
			Map<String,String> valuesMap = hasCompMap.get(rowKey);
			String showIndexs = ""; // like B1,null,null,B2,null,null,B3,null
			
			//抽取该列第一个指标 判断该指标所属的正常列排序是什么
			for(Iterator<String> it2 = valuesMap.keySet().iterator(); it2.hasNext();){
				String index = it2.next();
				if(rowTdMap.containsKey(index)){
						showIndexs = rowTdMap.get(index);
						break;
				}
			}
			
			//结合 th和td 取出展示出来的指标及同比环比
			StringBuffer tempValue = new StringBuffer();
//			tempValue.append(rowKey+";");
			
			String[] showIndexTemp = showIndexs.split(",");
			String lastIndex = "";
			for (int i = 0 ; i< showIndexTemp.length; i++) {
				String showIndex = showIndexTemp[i];
				String thValue = colThTwo[i];
				String appValue =  "";
				if(!"null".equals(showIndex)){ //正常值
					String value=valuesMap.get(showIndex);
					if(value==null)
						appValue="--";
					else
						appValue = value.split(";")[0];
					String type=indexTypeMap.get(showIndex);
					if("1".equals(type)){
						if("环比".equals(thValue))
							appValue=vt.trans2RateWithColor(appValue);
						else
							appValue=vt.comRate(appValue);
					}
					lastIndex = showIndex;
				}else{ //同比、周比
					int index = thIndexMap.get(thValue);
					String type=indexTypeMap.get(lastIndex);
					if(index ==1 || index == 3){	//昨日， 上周
						if(!valuesMap.containsKey(lastIndex))
							appValue = "--";
						else
							appValue = valuesMap.get(lastIndex).split(";")[index];
						if("1".equals(type)){ //如果是百分比类型
							appValue=vt.comRate(appValue);
						}
					}else if(index ==2 || index == 4){
						if(!valuesMap.containsKey(lastIndex))
							appValue = "0.00%";
						else
							appValue = valuesMap.get(lastIndex).split(";")[index];
					}
					
				}
				tempValue.append(appValue+";");
			}
			dataMap.put(rowKey, tempValue.toString());
//			finalData.add(tempValue.toString());
		}
		
		if(hasGroupBy){ //分组groupBy1,groupBy2,参见黄页运营日报的城市&品类数据。
			Set<String> rowKeySet = new HashSet<String>();
			for(String head1: rowHeadList_Tmp){
				for(String head2: thTwoList_Tmp){
					String keyString = head1+"_"+head2;
					if(!rowKeySet.contains(keyString) && dataMap.containsKey(keyString)){
						rowKeySet.add(keyString);
						rowHeadList.add(head1);
						thTwoList.add(head2);
						rowspanList.add(1);
					}
				}
			}
		}
		
		/**默认按照第一列指标值降序排序*/
//		finalData = SortUtil.sortIEListByFileName(finalData, 1, 2);
		conMap.put("rowHeadList",rowHeadList);
		conMap.put("thTwoList",thTwoList);
		conMap.put("rowspanList",rowspanList);
		conMap.put("valuesMap",dataMap);
//		System.out.println(dataMap.keySet());
		
		content = VelocityTools.render(content, conMap);
		return content;
	}
	
	//计算公式指标

	public List<IETableSearch> evalValue(List<IETableSearch> speSearchList,Map<String,Object> valueMap){
		List<IETableSearch> speList=new ArrayList<IETableSearch>();
		
		
		for(IETableSearch vo : speSearchList){
			String index = vo.getIndexs().trim().split(":")[0];
			String formula = vo.getSearchSql();
			if(MathEval.checkIndexBySelfRow(formula, valueMap.keySet())){
//				System.out.println(formula);
				//检查该指标的计算公式是否仅依赖该该行内的指标
				String countFormula = formula;
				//替换值  放入计算公式
				for(Iterator<String> it2 = valueMap.keySet().iterator() ; it2.hasNext();){
					String key2 = it2.next();
					Object value = valueMap.get(key2);
					countFormula =replaceKey(countFormula,key2,String.valueOf(value));
				}
				String famount = "--";
//				System.out.println(countFormula);
//				if(!StringUtils.contains(countFormula, "--"))
				famount = String.valueOf(MathEval.eval(countFormula.replace("--", "0")));
//				System.out.println(famount);
				valueMap.put(index, famount);
			}else{
				speList.add(vo);
			}
	  }
		return speList;
  }
	
	private static String pattern = "(^|\\W)(?)(\\W|$)"; //正则
	/**
	 * 替换相应指标值，考虑到有A+AB这种情况出现，不能直接用replace,用正则替换
	 * @param formula	公式
	 * @param key  指标
	 * @param value  值
	 * @return
	 */
	public String replaceKey(String formula,String key,String value){
		Pattern p = Pattern.compile(pattern.replace("?", key));
		Matcher m=p.matcher(formula);
//		System.out.println("begin: "+formula+"---"+key+":"+value);
		while(m.find()){
			StringBuffer sb = new StringBuffer();
			m.appendReplacement(sb, m.group(1)+value+m.group(3));
			m.appendTail(sb);
			formula=sb.toString();
			m=p.matcher(formula);
		}
//		System.out.println("end: "+formula);
		return formula;
	}

//	private static List<String> thIndex =  new ArrayList<String>();
//	static{
//		thIndex.add("昨日");
//		thIndex.add("环比");
//		thIndex.add("上周");
//		thIndex.add("同比");
//	}
	private static Map<String, Integer> thIndexMap =  new HashMap<String, Integer>();
	static{
		thIndexMap.put("昨日", 1);
		thIndexMap.put("日比", 2);
		thIndexMap.put("上周", 3);
		thIndexMap.put("周比", 4);
	}	
	
	public String getResultStr(String rowKey,Map<String,Map<String,String>> hasCompMap,Map<String,String>  rowTdMap,
			HashMap<String,String> indexTypeMap,String[] thTemps,boolean hasGroup,int tempType){
		Map<String,String> valuesMap = hasCompMap.get(rowKey);
		String showIndexs = ""; // like B1,null,null,B2,null,null,B3,null
		
		//抽取该列第一个指标 判断该指标所属的正常列排序是什么
		for(Iterator<String> it2 = valuesMap.keySet().iterator(); it2.hasNext();){
			String index = it2.next();
			
			if(rowTdMap.containsKey(index)){
				showIndexs = rowTdMap.get(index);
			}
			if(!"".equals(showIndexs)){
				break;
			}
		}
		
		//结合 th和td 取出展示出来的指标及同比环比
		StringBuffer tempValue = new StringBuffer();
		tempValue.append(rowKey+";");
		
		String[] showIndexTemp = showIndexs.split(",");
		
		String lastIndex = "";
		for (int i = 0 ; i< showIndexTemp.length; i++) {
			String showIndex = showIndexTemp[i];
			int j = i + 1;
			if(tempType == 4) //模板4
				j = i;
			String thValue = thTemps[j];
			String appValue =  "";
			if(!"null".equals(showIndex)){ //正常值
//				System.out.println(rowKey+";"+showIndex);
				if(!valuesMap.containsKey(showIndex) && hasGroup){
					appValue = "--";
				}else {
					appValue = valuesMap.get(showIndex).split(";")[0];
				}
				String type=indexTypeMap.get(showIndex);
				if("1".equals(type)){ //如果是百分比类型
					if("环比".equals(thValue))
						appValue=vt.trans2RateWithColor(appValue);
					else
						appValue=vt.comRate(appValue);
				}
				lastIndex = showIndex;
			}else{ //昨日,日比,上周,周比
				int index = thIndexMap.get(thValue);
				String type=indexTypeMap.get(lastIndex);
				if(index ==1 || index == 3){	//昨日， 上周
					if(!valuesMap.containsKey(lastIndex) && hasGroup)
						appValue = "--";
					else
						appValue = valuesMap.get(lastIndex).split(";")[index];
					if("1".equals(type)){ //如果是百分比类型
						appValue=vt.comRate(appValue);
					}
				}else if(index ==2 || index == 4){
					if(!valuesMap.containsKey(lastIndex) && hasGroup)
						appValue = "0.00%";
					else
						appValue = valuesMap.get(lastIndex).split(";")[index];
				}
			}
//			if("--".equals(appValue)){			
//				appValue = "0";
//			}
			if("--".equals(appValue)){
				if(!valuesMap.containsKey(showIndex))
				{
					appValue = "0";
				}
				else if("--".equals(valuesMap.get(showIndex).split(";")[0]))//本身是--，1、 代表指标无意义  2、数据库中查出是null
				{
					appValue = "--";
				}
				else appValue = "0";
			}
			tempValue.append(appValue+";");
		}
		return tempValue.toString();
	}
	
	
	/**
	 * 替换日期字段
	 * @param sql
	 * @param date
	 * @param lastDay
	 * @param tomorrow
	 * @return
	 */
	public String replaceDate(String sql, String date,String lastDay,String tomorrow){
		String curMonth =  DateUtil.getMonth(date,"yyyy-MM-dd","yyyyMM");
		
		String firDayMon=DateUtil.firstDayMonthBefore(date, "yyyy-MM-dd", 0); //当月第一天
		String lastDayMon=DateUtil.lastDayMonthBefore(date, "yyyy-MM-dd", 0); //当月最后一天
		String firDayLastMon=DateUtil.firstDayMonthBefore(date, "yyyy-MM-dd", 1); //上月第一天
		String lastDayLastMon=DateUtil.lastDayMonthBefore(date, "yyyy-MM-dd", 1); //上月最后一天
		
		sql = sql.replace("#date#",date);
		sql = sql.replace("#month#",curMonth);
		sql = sql.replace("#date0#",lastDay);
		sql = sql.replace("#date2#",tomorrow);
		sql = sql.replace("#firstDay#",firDayMon);
		sql = sql.replace("#lastDay#",lastDayMon);
		sql = sql.replace("#firstDayLastMonth#",firDayLastMon);
		sql = sql.replace("#lastDayLastMonth#",lastDayLastMon);
		return sql;
	}
}
